const express = require('express');

const routes = express.Router();

//引入mysql
const mysql = require('mysql');
//创建连接池
var pool = mysql.createPool({
    connectionLimit : 10,
    host : 'localhost',
    user : 'root',
    password : 'root',
    database : 'personmessage'
})

routes.route('/')
    .get( (req,resp)=>{

        //获取cookies
        var uid = req.cookies.uid;
        //获取cookeis的当前职务
        var port_id = req.cookies.port_id;
        var time = req.cookies.getTimes;
        var loginame = req.cookies.loginame;

        //console.log(time)
        var hous = new Date(eval(time)).getHours();
        //console.log(hous)
        var minutes = new Date(eval(time)).getMinutes();
        var seconds = new Date(eval(time)).getSeconds();
        var houss = (hous < 10) ? '0'+hous : hous;
        var minus = (minutes < 10) ? '0'+minutes : minutes;
        var secondss = (seconds < 10) ? '0'+seconds : seconds;
        var date = houss+':'+minus+':'+secondss;

        //console.log(sub_id)
        //每一页把条数据
        var param = 8;
        //将当前的页码传递出去作比较
        //如果首次登录教师账号  params 为空的情况下让它自动生成一个值 然后直接与下方list进行数学运算 默认查询第一页的数据
        var pos = (req.query.params) ? req.query.params : 1;
        //console.log(pos)
        //获取查询的页条数的起始位置
        var list = (pos * param) - param;

        //console.log(getTime)
        var sql = `SELECT DISTINCT pay.* , permsg.uname , login.port_id , education.edu_money , station.sta_money FROM pay LEFT JOIN education ON education.id = pay.edu_id LEFT JOIN station ON station.id = pay.sta_id LEFT JOIN permsg ON permsg.uid = pay.uid LEFT JOIN login ON login.port_id = pay.port_id LIMIT ${list} , ${param}`;
        //console.log(sql)
        pool.getConnection(function(err,connection){
            if(err) throw err;
            //    链接成功
            connection.query(sql,(error,result)=> {

                var sql_pay = `SELECT COUNT(*) as sum FROM pay`
                //    链接成功
                connection.query(sql_pay,(e,res)=> {
                //    释放连接   关闭数据库
                connection.release();
                    //判断职务
                    if(port_id == 1){
                        var port = '普通员工';

                    }else if(port_id == 2){
                        var port = '行政主管';

                    }else if(port_id == 3){
                        var port = '财务总监';

                    }
                    var sum = res[0].sum;
                    //判断需要多少页  条数不能为小数点  需要向上取整
                    var params = Math.ceil( sum / param );
                    //获取当前时间戳  Date.now()
                    var data = new Date().getTime();
                    resp.render('sle_msgpay',{
                        port_id : port_id,
                        result : result,
                        port : port,
                        date : date,
                        loginame : loginame,
                        uid : uid,
                        params : params,
                        pos : pos,  //当前页数

                    })
                    //console.log(res)

                    if(e) throw e;
                })

                //sql语句出错执行报错处理
                if(error) throw error;
            })
        })
    } )


//暴露接口
module.exports = routes;